package com.ldzy.webase.dao;

import com.ldzy.webase.entity.Comment;
import com.ldzy.webase.entity.Press;
import com.ldzy.webase.entity.User;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

/**
 * @author ：Lq
 * @version ：1.0
 * @date ：2022/10/25 20:49
 * @description ：t_user-用户表-数据层
 */
public interface UserMapper {
    // 登陆
    // 1.学号-密码 2.手机号-密码，组合登陆
    @Select("select * from t_user where studentId=#{studentId} and password=#{password} " +
            "or phoneNumber=#{studentId} and password=#{password}")
    User loginByStudentIdOrPhoneNumber(User user);

    // 注册 (成功返回1，失败返回0)
    @Insert("insert into t_user(username,password,sex,studentId,department,avatar,birthday,phoneNumber,question,answer) " +
            "values(#{username},#{password},#{sex},#{studentId}," +
            "#{department},#{avatar},#{birthday},#{phoneNumber},#{question},#{answer})")
    int register(User user);

    // 根据学生uid查询密码 (其一用途：用于验证token)
    @Select("select password from t_user where uid=#{uid}")
    String getPasswordByUid(Integer uid);

    // 根据学生uid查询最后登陆时间 (其一用途：用于验证登陆时间是否超过1天，否则需要重新登陆)
    @Select("select endLoginTime from t_user where uid=#{uid}")
    Timestamp getEndLoginTimeByUid(Integer uid);

    // 根据学生uid修改最后登陆时间 (其一用途：用于验证登陆时间是否超过1天，否则需要重新登陆)
    @Update("update t_user set endLoginTime=#{endLoginTime} where uid=#{uid}")
    int editEndLoginTimeByUid(String endLoginTime, Integer uid);

    // 根据学生uid查询个人基本信息 (其一来源：uid来源于token)
    @Select("select " +
            "u.username,u.sex,u.studentId,u.department," +
            "u.avatar,u.birthday,u.trueName,u.phoneNumber," +
            "u.introduce,u.email,u.qqNumber,u.wxNumber,u.role," +
            "(select COUNT(*) from t_press where uid = u.uid) as publishNum," +
            "(select COUNT(*) from t_comment where uid = u.uid) as commentNum," +
            "(select COUNT(*) from t_presslike where uid = u.uid) as likeNum " +
            "from t_user as u " +
            "where u.uid=#{uid}")
    User getUserInfo(Integer uid);

    // 根据学生uid修改个人基本信息 (其一来源：uid来源于token)
    @Update("update t_user set " +
            "avatar=#{avatar},username=#{username},introduce=#{introduce},sex=#{sex}," +
            "birthday=#{birthday},qqNumber=#{qqNumber},wxNumber=#{wxNumber},department=#{department} " +
            "where uid=#{uid}")
    int editUserInfoByUid(User user);

    // 查询用户所有已发布的咨询
    @Select("<script>" +
            "select p.*,(select COUNT(*) from t_comment as c where c.pressId = p.pressId) as commentNum," +
            "(select COUNT(*) from t_presslike as plk where plk.pressId = p.pressId) as likeNum," +
            "u.username,u.avatar,con.consultImage,con.consultName " +
            ",(select COUNT(*) from t_presslike where pressId=p.pressId and uid=#{uid}) as likeStatus " +
            "from t_press as p " +
            "left join t_user u on u.uid = p.uid " +
            "left join t_consult con on con.consultId = p.consultId " +
            "<where> " +
            "p.uid=#{uid} " +
            "</where>" +
            "order by " + // 排序
            "p.publishDate desc " +
            "</script>")
    List<Press> getPressByUid(@Param("uid") Integer uid);

    // 查询用户所有已发表的评论
    @Select("<script>" +
            "select " +
            "com.*,u.username,u.avatar,p.content as pressContent,p.pressImages," +
            "(select username from t_user where uid = p.uid) as pressUsername " +
            "from " +
            "t_comment as com " +
            "left join t_press p on p.pressId = com.pressId " +
            "left join t_user u on u.uid = com.uid " +
            "<where> " +
            "com.uid=#{uid} " +
            "</where>" +
            "order by " + // 排序
            "com.commentDate desc " +
            "</script>")
    List<Comment> getCommentByUid(@Param("uid") Integer uid);

    // 根据咨询pressId和用户uid，删除对应的咨询表t_press中的数据，删除对应的评论表，删除对应的咨询点赞表
    @Delete("delete t_comment,t_presslike,t_press from t_press " +
            "left join t_comment on t_press.pressId = t_comment.pressId " +
            "left join t_presslike on t_press.pressId = t_presslike.pressId " +
            "where t_press.pressId = #{pressId} and t_press.uid = #{uid}")
    int delPressByPressIdAndUid(@Param("pressId") Integer pressId, @Param("uid") Integer uid);

    // 根据评论commentId和用户uid，删除对应的评论表
    @Delete("delete from t_comment where commentId=#{commentId} and uid=#{uid}")
    int delCommentByCommentIdAndUid(@Param("commentId") Integer commentId, @Param("uid") Integer uid);

    // 根据咨询pressId和用户uid，编辑对应的咨询表t_press中的数据
    @Update("<script>" +
            "update t_press set " +
            "content=#{press.content},pressImages=#{press.pressImages},publishProvince=#{press.publishProvince}" +
            "<if test=\"press.wxNumber != null\"> " +
            ",wxNumber=#{press.wxNumber}" +
            "</if>" +
            "<if test=\"press.qqNumber != null\"> " +
            ",qqNumber=#{press.qqNumber}" +
            "</if>" +
            "<if test=\"press.phoneNumber != null\"> " +
            ",phoneNumber=#{press.phoneNumber}" +
            "</if>" +
            "<where> " +
            "pressId = #{press.pressId} and t_press.uid = #{press.uid} " +
            "</where>" +
            "</script>")
    int editPressByPressIdAndUid(@Param("press") Press press);

    // 根据学生uid修改邮箱 (其一来源：uid来源于token)
    @Update("update t_user set " +
            "email=#{email} where uid=#{uid}")
    int editUserEmailByUid(User user);

    // 根据邮箱获取学生uid (其一来源：email来源于前端传参)
    @Select("select uid from t_user " +
            "where email=#{email}")
    String getUidByEmail(@Param("email") String email);

    // 根据学生uid修改密码 (其一来源：uid来源于token)
    @Update("update t_user set " +
            "password=#{password} where uid=#{uid}")
    int editPwdByUid(User user);

    // 根据学生uid、旧密码修改密码 (其一来源：uid来源于token)
    @Update("update t_user set " +
            "password=#{newPassword} where uid=#{uid} and password=#{oldPassword}")
    int editPwdByUidAndOldPassword(@Param("uid") Integer uid, @Param("newPassword") String newPassword, @Param("oldPassword") String oldPassword);

    // 根据学号studentId获取学生信息 (其一用途：注册时提前查询学号是否被占用)
    @Select("select * from t_user " +
            "where studentId=#{studentId}")
    User getUserInfoByStudentId(@Param("studentId") String studentId);

    // 根据手机号phoneNumber获取学生信息 (其一用途：注册时提前查询手机号是否被占用)
    @Select("select * from t_user " +
            "where phoneNumber=#{phoneNumber}")
    User getUserInfoByPhoneNumber(@Param("phoneNumber") String phoneNumber);

    // 根据邮箱email获取学生信息 (其一用途：绑定/更绑邮箱时提前查询邮箱是否被占用)
    @Select("select * from t_user " +
            "where email=#{email}")
    User getUserInfoByEmail(@Param("email") String email);
}
